
package com.productmodule.dao;
import com.productmodule.bean.TbProductPrice;

import java.sql.*;
import java.util.*;

import util.*;
public class TbProductPriceDAO {
    private Connection conn = null;
    private PreparedStatement pt = null;
    private Statement st = null;
    private ResultSet rs = null;
    private boolean flag = false;
    private String sql = "";
    private TbProductPrice tbProductPrice=null;
    private List<TbProductPrice> tbProductPriceList=null;

    public boolean add(TbProductPrice inTbProductPrice){
	try{
    		
    		conn=ConnectDB.getSqlServerConnection();
    		Hashtable<String,String> ht = inTbProductPrice.getHt();
    		st = conn.createStatement();
    		if(ht.size()>0){
    			System.out.println("ht.size==="+ht.size());
    			Iterator it = ht.keySet().iterator();
    			while(it.hasNext()){
    				String key = it.next().toString();
    				String valu = ht.get(key);
    				String hql = "insert into Tb_ProductPrice(CompanyId,ProductId,ProductUnitId,InterfaceId,ProductPirce,UserId,ProductDate) " +
    						"values("+inTbProductPrice.getCompanyId()+","+inTbProductPrice.getProductId()+","+inTbProductPrice.getProductUnitId()+","+key
    						+","+valu+","+inTbProductPrice.getUserId()+","+inTbProductPrice.getProductDate()+")";
    				System.out.println("hql==="+hql);
    				st.addBatch(hql);
    			}
    			st.executeBatch();
    		}else{
    			sql="insert into Tb_ProductPrice(CompanyId,ProductId,ProductUnitId,InterfaceId,ProductPirce,UserId,ProductDate) values("+inTbProductPrice.getCompanyId()
        		+","+inTbProductPrice.getProductId()+","+inTbProductPrice.getProductUnitId()+","+inTbProductPrice.getInterfaceId()
        		+","+inTbProductPrice.getProductPirce()+","+inTbProductPrice.getUserId()+",'"+inTbProductPrice.getProductDate()+"')";
    			st.addBatch(sql);
    			st.executeBatch();
    		}
    		flag=true;
    	}catch(Exception e){
    		e.printStackTrace();
    		flag=true;
    	}finally {
    		ConnectDB.closeSqlConnection();        }
    	return flag;
    }
   
    public boolean del(int inCompanyProductMiddleId){
        return UtilDAO.del("Tb_ProductPrice", "CompanyProductMiddleId", inCompanyProductMiddleId);
    }
    public boolean edit(TbProductPrice inTbProductPrice){
        try{
            
        conn=ConnectDB.getSqlServerConnection();
        sql="update Tb_ProductPrice set CompanyId=?,ProductId=?,ProductUnitId=?,InterfaceId=?,ProductPirce=?,CreditRatingId=?,UserId=?,CreateTime=getdate() where CompanyProductMiddleId=?";
        pt = conn.prepareStatement(sql);
        
        pt.setInt(1,inTbProductPrice.getCompanyId());
        pt.setInt(2,inTbProductPrice.getProductId());
        pt.setInt(3,inTbProductPrice.getProductUnitId());
        pt.setInt(4,inTbProductPrice.getInterfaceId());
        pt.setDouble(5,Double.valueOf(inTbProductPrice.getProductPirce()));
        pt.setInt(6,inTbProductPrice.getCreditRatingId());
        pt.setInt(7,inTbProductPrice.getUserId());
        pt.setInt(8,inTbProductPrice.getCompanyProductMiddleId());
        if (pt.executeUpdate() > 0) flag=true;
        }catch(Exception e){
            e.printStackTrace();
        }finally {
                        ConnectDB.closeSqlConnection();        }
        return flag;
    }
    public TbProductPrice getTbProductPriceByCompanyProductMiddleId(int inCompanyProductMiddleId) {
        tbProductPrice=null;
        try{
            conn = ConnectDB.getSqlServerConnection();
            sql = "select * from Tb_ProductPrice where CompanyProductMiddleId=? ";
            pt = conn.prepareStatement(sql);
            pt.setInt(1,inCompanyProductMiddleId);
            rs = pt.executeQuery();
            while (rs.next()) {
                if (tbProductPrice == null) tbProductPrice = getTbProductPrice(rs);
            }
        }catch(Exception e){
            e.printStackTrace();
        }finally {
                        ConnectDB.closeSqlConnection();        }
        return tbProductPrice;
    }
    public List<TbProductPrice> getTbProductPriceAll() {
        tbProductPriceList=new ArrayList<TbProductPrice>();
        try{
            conn = ConnectDB.getSqlServerConnection();
            sql = "select * from Tb_ProductPrice";
            pt = conn.prepareStatement(sql);
            rs = pt.executeQuery();
            while (rs.next()) {
                tbProductPriceList.add(getTbProductPrice(rs));
            }
        }catch(Exception e){
            e.printStackTrace();
        }finally {
                        ConnectDB.closeSqlConnection();        }
        return tbProductPriceList;
    }
	public List<TbProductPrice> getTbProductPriceAll(VskPage vp) {
        tbProductPriceList=new ArrayList<TbProductPrice>();
        try{
            conn = ConnectDB.getSqlServerConnection();
            sql = "select * from Tb_ProductPrice";
            vp.setAllCountSql(sql);
            vp.getAllRecordCount(conn, vp.getAllCountSql());
            pt = conn.prepareStatement(vp.processPageSql( "Tb_ProductPrice", "CompanyProductMiddleId"));
            rs = pt.executeQuery();
            while (rs.next()) {
                tbProductPriceList.add(getTbProductPrice(rs));
            }
        }catch(Exception e){
            e.printStackTrace();
        }finally {
                        ConnectDB.closeSqlConnection();        }
        return tbProductPriceList;
    }
	/**
	 * 
	 *@author qingyu zhang
	 *@function:
	 * @param tbProductPrice
	 * @return
	 *2011-6-27
	 *下午01:36:45
	 *ProjectManagerT
	 *com.productpricemodule.dao
	 *TbProductPrice
	 */
	public Map<Integer,String> getProductPrice(TbProductPrice productPrice){
		  Map<Integer, String> map = new HashMap<Integer, String>();
		  
	        try{
	            conn = ConnectDB.getSqlServerConnection();
	            sql = "select * from Tb_ProductPrice where ProductDate="+productPrice.getProductDate()+" and ProductId="+productPrice.getProductId();
	            pt = conn.prepareStatement(sql);
	            rs = pt.executeQuery();
	            while (rs.next()) {
	                TbProductPrice product = new TbProductPrice();
	                product = getTbProductPrice(rs);
	                Integer key = product.getInterfaceId();
	                String val = product.getProductPirce();
	                map.put(key, val);
	            }
	        }catch(Exception e){
	            e.printStackTrace();
	        }finally {
	                        ConnectDB.closeSqlConnection();        
	                     }
	        return map;
	}
	/**
	 * 根据年份/产品ID删除产品价格表中的记录
	 *@author qingyu zhang
	 *@function:
	 * @param condition
	 * @return
	 *2011-6-27
	 *下午03:54:14
	 *ProjectManagerT
	 *com.productpricemodule.dao
	 *boolean
	 */
	public boolean del(String condition){
		return UtilDAO.delAll(condition);
	}
	public int getProductByProductId(int productId){
		int num = 0;
		  try {
			conn = ConnectDB.getSqlServerConnection();
			sql="select count(*) si from Tb_ProductPrice where ProductId="+productId;
			pt = conn.prepareStatement(sql);
			rs = pt.executeQuery();
			if(rs.next()){
				num = rs.getInt("si");
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
            ConnectDB.closeSqlConnection();        
        }
		return num;
	}
	/**
	 * get info of product By productId
	 *@author qingyu zhang
	 *@function:
	 * @param productId
	 * @return
	 *2011-7-2
	 *下午04:46:05
	 *ProjectManagerT
	 *com.productmodule.dao
	 *List<TbProductPrice>
	 */
	public List<TbProductPrice> getTbProductPriceByProductId(int productId) {
        tbProductPriceList=new ArrayList<TbProductPrice>();
        try{
            conn = ConnectDB.getSqlServerConnection();
            sql = "select * from Tb_ProductPrice where ProductId="+productId;
            pt = conn.prepareStatement(sql);
            rs = pt.executeQuery();
            while (rs.next()) {
                tbProductPriceList.add(getTbProductPrice(rs));
            }
        }catch(Exception e){
            e.printStackTrace();
        }finally {
                        ConnectDB.closeSqlConnection();        }
        return tbProductPriceList;
    }
    public TbProductPrice getTbProductPrice(ResultSet rs) throws Exception{
        TbProductPrice tbProductPrice = new TbProductPrice();
        tbProductPrice.setCompanyProductMiddleId(rs.getInt("CompanyProductMiddleId"));
        tbProductPrice.setCompanyId(rs.getInt("CompanyId"));
        tbProductPrice.setProductId(rs.getInt("ProductId"));
        tbProductPrice.setProductUnitId(rs.getInt("ProductUnitId"));
        tbProductPrice.setInterfaceId(rs.getInt("InterfaceId"));
        tbProductPrice.setProductPirce(rs.getString("ProductPirce"));
        tbProductPrice.setCreditRatingId(rs.getInt("CreditRatingId"));
        tbProductPrice.setUserId(rs.getInt("UserId"));
        tbProductPrice.setCreateTime(rs.getString("CreateTime"));
        tbProductPrice.setProductDate(rs.getString("ProductDate"));
        return tbProductPrice;
    }
    
}
